package com.ujiuye.dao.impl;

import com.ujiuye.dao.StudentDao;
import com.ujiuye.model.Page;
import com.ujiuye.model.Student;
import com.ujiuye.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.Constructor;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    QueryRunner qr = DruidUtils.getQueryRunner();
    @Override
    public int saveStudent(Student student) {

        String sql = "insert into stu values(0,?,?,?,?,?)";
        int row = 0;
        try {
            row = qr.update(sql, student.getSname(), student.getGender(),
                    student.getSbir(), student.getHobby(), student.getPhoto());
        } catch (SQLException throwables) {
            throw new RuntimeException(throwables);
        }
        return row;
    }

    @Override
    public long getStuCount(Student student) {
        StringBuilder sb = new StringBuilder( "select count(sid) from stu where 1 = 1");
        conditionSql(student,sb);
//        String sql  = "select count(sid) from stu where 1 = 1";

        String sql = sb.toString();
        Long lo = null;
        try {
            lo = qr.query(sql,new ScalarHandler<Long>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lo;
    }

    private void conditionSql(Student student, StringBuilder sb) {

// sql 语句的拼接,注意: and 前面必须加上空格
        String sname = student.getSname();
        if(sname != null && sname.trim() != ""){
            sb.append(" and sname like '%" +sname + "%' " );
        }
        String gender = student.getGender();
        if (gender != null && !gender.equals("-1") && gender.trim() != "") {
            sb.append(" and gender = '" + gender + "' " );
        }

    }

    @Override
    public List<Student> selectStudentPage(int start, int size, Student student) {
        StringBuilder sb = new StringBuilder( "select * from stu where 1 = 1");
        conditionSql(student,sb);
        sb.append(" limit ?,?");  //分页关键字拼接
        String sql = sb.toString();
        List<Student> stuList = null;
        try {
            stuList = qr.query(sql, new BeanListHandler<>(Student.class), start, size);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return stuList;

    }

    @Override
    public int deleteStudentById(int id) {
        String sql = "delete from stu where sid = ? ";
        int row = 0;
        try {
            row = qr.update(sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return row;
    }

    @Override
    public Student getStudentById(int id) {
        String sql = "select * from stu where sid = ? ";
        Student student = null;
        try {
            student = qr.query(sql, new BeanHandler<>(Student.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public int updateStudentDo(Student student) {
        String sql = "update stu set sname=? ,gender=? ,sbir=? ,hobby=? , photo=? where sid = ? ";
        int row = 0 ;
        try {
            row = qr.update(sql,student.getSname(),student.getGender(),
                    student.getSbir(),student.getHobby(),student.getPhoto(),student.getSid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return row;
    }



}
